﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class ScoreRepository : BaseRepository
    {
        /// <summary>
        /// 根据相应条件查询积分信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<U_Score_EX> QueryScoreList(ScoreSearch search, PageView view)
        {
            string cols = @" Id, 
                            MemberId, 
                            (ISNULL (InCome,0.00)+ISNULL(OutPay,0.00))  as Total, 
                            Balance, 
                            CreateTime, 
                            CreateUser, 
                            Status, 
                            Type, 
                            ProjectId, 
                            Remark";
            string sqlWhere = " and memberid=" + search.MemberId;
            //if (!string.IsNullOrEmpty(Convert.ToString(search.MemberId)))
            //{
            //    sqlWhere += "AND [MemberId] LIKE '%" + search.MemberId + "%'";
            //}
            return base.PageGet<U_Score_EX>(view, cols, @" [U_Score] ", sqlWhere, "[CreateTime] DESC", "");
        }
        /// <summary>
        /// 根据编号获得积分对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public U_Score GetScore(int? id)
        {
            string sql = @" SELECT [Id]
              ,[MemberId]
              ,[InCome]
              ,[OutPay]
              ,[Balance]
              ,[CreateTime]
              ,[CreateUser]
              ,[Status]
              ,[Type]
              ,[ProjectId]
              ,[Remark]
          FROM [U_Score ]
              WHERE Id=@Id";
            return base.Get<U_Score>(sql, new { Id = id });
        }
        /// <summary>
        /// 根据编号获得积分list
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<U_Score> GetScoreByMemeberId(int? id)
        {
            string sql = @" SELECT [Id]
              ,[MemberId]
              ,[InCome]
              ,[OutPay]
              ,[Balance]
              ,[CreateTime]
              ,[CreateUser]
              ,[Status]
              ,[Type]
              ,[ProjectId]
              ,[Remark]
          FROM [U_Score ]
              WHERE MemberId=@MemberId";
            return base.Query<U_Score>(sql, new { MemberId = id });
        }
        /// <summary>
        /// 添加积分
        /// </summary>
        /// <param name="score"></param>
        /// <returns></returns>
        public int InsertScore(U_Score score)
        {
            return (int)base.Insert(score);
        }
        /// <summary>
        /// 删除积分信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteScore(int? id)
        {
            string sql = " DELETE FROM [U_Score] WHERE Id=@Id";
            return base.Excute(sql, new { Id = id });
        }
        /// <summary>
        /// 修改积分信息
        /// </summary>
        /// <param name="score"></param>
        /// <returns></returns>
        public int UpdateScore(U_Score score)
        {
            //            string sql = @"Update U_Score set 
            //                            Id=@Id
            //                          ,MemberId=@MemberId
            //                          ,InCome=@InCome
            //                          ,OutPay=@OutPay
            //                          ,Balance=@Balance
            //                          ,CreateTime=@CreateTime
            //                          ,CreateUser=@CreateUser
            //                          ,Status=@Status
            //                          ,Type=@Type
            //                          ,ProjectId=@ProjectId
            //                          ,Remark=@Remark";
            return base.Update(score);
        }

        //根据MemberId 获取最新的积分
        public U_Score GetLastScoreByMemberId(int memberId)
        {
            string sql = @"SELECT top 1 [Id],[MemberId],[InCome],[OutPay],[Balance],[CreateTime],[CreateUser],[Status],[Type],[ProjectId],[Remark] 
                            FROM U_Score
                            where [MemberId]=@MemberId order by [Id] desc";
            return base.Get<U_Score>(sql, new { MemberId = memberId });
        }

        //检验今天是否已经签到
        public int CheckSignUpToday(int memberId)
        {
            string sql = @"select COUNT(id) AS [COUNT] from U_Score where MemberID=@MemberID and CONVERT(char(10),CreateTime,120)=CONVERT(char(10),GETDATE(),120)";
            return base.Get<int>(sql, new { MemberID = memberId });

        }
        //会员积分
        public int GetScoreByMID(int memberId)
        {
            string sql = "select ISNULL((SUM(Income)-SUM(OutPay)),0)AS Score from U_Score  where MemberId=@MemberId";
            return base.Get<int>(sql, new { MemberID = memberId });
        }

        public int SaveScoreSetting(U_ScoreSetting scoreSetting)
        {
            if (scoreSetting.Id > 0)
            {
                return (int)base.Update(scoreSetting);
            }
            return (int)base.Insert(scoreSetting);

        }
        public PagedList<U_ScoreSetting> QueryScoreSettingList(ScoreSearch search, PageView view)
        {
            string cols = @" * ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.SettingName))
            {
                sqlWhere = " and name='" + search.SettingName + "'";
            }
            return base.PageGet<U_ScoreSetting>(view, cols, @" [U_ScoreSetting] ", sqlWhere, "[Id] asc", "");
        }

        public U_ScoreSetting GetScoreSetting(int? id)
        {
            const string sql = @" SELECT  *
                FROM [U_ScoreSetting ]
              WHERE Id=@Id";
            return base.Get<U_ScoreSetting>(sql, new { Id = id });
        }

        public U_ScoreSetting GetScoreSetting(string name)
        {
            const string sql = @" SELECT  *
                FROM [U_ScoreSetting ]
              WHERE Name=@Name";
            return base.Get<U_ScoreSetting>(sql, new { Name = name });
        }



        #region 签到
        public bool Ischeck(int memberid)
        {
            const string sql = @" select ISNULL( DATEDIFF(day, max(checktime),getdate()) ,-1) as ischck from U_Check where memberid=@memberid";
            return base.Get<int>(sql, new { memberid = memberid }) == 0;
        }
        public int SaveCheck(U_Check check)
        {
            if (check.Id > 0)
            {
                return (int)base.Update(check);
            }
            return (int)base.Insert(check);
        }

        public PagedList<U_Check> QueryCheckList(CheckSearch search, PageView view)
        {
            string cols = @" * ";
            string sqlWhere = "";
            if (search.MemberId != 0)
            {
                sqlWhere = " and memberid='" + search.MemberId + "'";
            }
            return base.PageGet<U_Check>(view, cols, @" [U_Check] ", sqlWhere, "[Id] asc", "");
        }

        public List<U_Check_EX> QueryCheckList(string mid, int year, int month)
        {
            string sql = "select CheckTime from U_Check where MemberId=@MemberId and DATENAME(YY,CheckTime)=@Year and DATENAME(M,CheckTime)=@Month";
            return base.Query<U_Check_EX>(sql, new { MemberId = mid, Year = year, Month = month });
        }

        public U_Check GetLastCheck(int memberid)
        {
            const string sql = "select top 1  * from U_Check  where memberid=@memberid  order by checktime desc";
            return base.Get<U_Check>(sql, new { memberid = memberid });
        }
        #endregion

        public int GetCheckDay(int memberid)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 中奖名单
        /// </summary>
        /// <returns></returns>
        public List<T_Prize> GetPrizeList()
        {
            string strSql = "select top 100 * from T_prize where prize!='谢谢参与' order by ID desc ";
            return base.Query<T_Prize>(strSql, null);
        }

        public PagedList<T_Prize> MyLottery(PageView view, T_PrizeSearch search)
        {
            string cols = @"Prize,Score,CreateDate";
            string sqlWhere = " and MemberId=" + search.mid;
            return base.PageGet<T_Prize>(view, cols, @"[T_Prize] ", sqlWhere, "[CreateDate] DESC", "");
        }


        /// <summary>
        /// 大转盘 奖品列表
        /// </summary>
        /// <returns></returns>
        public List<T_Award> GetAwardList()
        {
            string strSql = @"SELECT [Id]
                          ,[AwardName]
                          ,[TotalNum]
                          ,[BalanceNum]
                          ,[Status]
                          ,[Pid]
                          ,[Remark]
                          ,[CreateDate]
                          ,[Prize]
                          ,[Type]
                          ,Score
                          ,Rate
                      FROM [T_Award] WHERE [Type]=1 AND [Status]=1 AND BalanceNum>0 order by Pid asc ";
            return base.Query<T_Award>(strSql, null);
        }

        /// <summary>
        /// 减数量
        /// </summary>
        /// <param name="awardId"></param>
        /// <returns></returns>
        public int MinAward(int awardId)
        {
            string sql = @"UPDATE [T_Award] SET BalanceNum=BalanceNum-1 WHERE Id=@Id";
            return base.Excute(sql, new { Id = awardId });
        }

    }
}
